/**
 * 
 */
package database.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import database.DBConnection;

import model.Attribute;
import model.subjects.Customer;
import model.subjects.EnterpriseCustomer;
import model.subjects.Person;
import model.subjects.PersonCustomer;

/**
 * @author Triin
 *
 */
public class CustomerDAO {
	static DBConnection db = new DBConnection();
	AttributeDAO attributeDao = new AttributeDAO();
	public static Customer[] findCustomersFromDB(){
		PersonCustomer[] personCustomers = findPersonCustomersFromDB();
		EnterpriseCustomer[] enterpriseCustomers = findEnterpriseCustomersFromDB();
		Customer[] customers = new Customer[personCustomers.length+enterpriseCustomers.length];
		int i;
		for(i=0;i<personCustomers.length;i++){
			customers[i]=personCustomers[i];
		}
		for(int j=0;j<enterpriseCustomers.length;j++){
			customers[i+j]= enterpriseCustomers[j];
		}
		return customers;
	}
	public static PersonCustomer[] findPersonCustomersFromDB(){
		String sql = "Select C.customer, P.person, P.first_name, P.last_name, P.identity_code, P.birth_date from customer C left join Person p ON C.subject_fk=P.person;";
		LinkedList<PersonCustomer> customersList = new LinkedList<PersonCustomer>();
		int i=0;
		try{
			ResultSet resultSet = db.executeQuery(sql);
			while(resultSet.next()){
				PersonCustomer customer = new PersonCustomer();
				customer.setCustomerId(resultSet.getInt("customer"));
				customer.setId(resultSet.getInt("person"));
				customer.setFirst_name(resultSet.getString("first_name"));
				customer.setLast_name(resultSet.getString("last_name"));
				customer.setIdentity_code(resultSet.getString("identity_code"));
				//Birth_date?
				customersList.add(customer);
				i++;
			}
			
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding personcustomers");
			e.printStackTrace();
		}
		PersonCustomer[] customers = new PersonCustomer[i];
		return customersList.toArray(customers);
	}
	public static EnterpriseCustomer[] findEnterpriseCustomersFromDB(){
		String sql = "Select C.customer, P.enterprise, E.name, E.full_name from customer C left join Enterprise E ON C.subject_fk=E.enterprise;";
		LinkedList<EnterpriseCustomer> customersList = new LinkedList<EnterpriseCustomer>();
		int i=0;
		try{
			ResultSet resultSet = db.executeQuery(sql);
			while(resultSet.next()){
				EnterpriseCustomer customer = new EnterpriseCustomer();
				customer.setCustomerId(resultSet.getInt("customer"));
				customer.setId(resultSet.getInt("enterprise"));
				customer.setName(resultSet.getString("name"));
				customer.setFullname(resultSet.getString("full_name"));
				customersList.add(customer);
				i++;
			}
			
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding enterprisecustomers");
			e.printStackTrace();
		}
		EnterpriseCustomer[] customers = new EnterpriseCustomer[i];
		return customersList.toArray(customers);
	}
	public LinkedList<Attribute> findCustomerAttributes(int customerId){
		String sql="SELECT C.customer, P.person,P.first_name, P.last_name, SAT.data_type, SAT.type_name, SA.value_text, SA.value_number, SA.value_date FROM customer C INNER JOIN person P ON  C.subject_fk=P.person " 
			+"LEFT JOIN subject_attribute SA ON C.customer=SA.subject_fk INNER JOIN subject_attribute_type SAT ON SA.subject_attribute_type_fk = SAT.subject_attribute_type "
			+"WHERE C.subject_type_fk=1 AND SA.subject_type_fk=4 AND C.customer ="+customerId+";";
		
		LinkedList<Attribute> attributes=null;
		
		try{
			ResultSet resultSet = db.executeQuery(sql);
			attributes = attributeDao.toAttributes(resultSet);
		}catch(SQLException e){
			System.out.println("A problem with SQL statement in finding attributes for customer");
			e.printStackTrace();
		}
		return attributes;
		
	}
	
	public static void makeCustomer(int subjectId, int subjectType){
		CallableStatement cs;
		try {
			db.connect();
			
			cs = db.getConnection().prepareCall("{call f_make_customer(?, ?)}");
			cs.setInt(1, subjectId);
			cs.setInt(2, subjectType);

			cs.execute();
			cs.close();
			db.closeConnection();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static LinkedList<Attribute> findAttributeNames(){
		return AttributeDAO.findAttributeNames(4);
	}
}
